import pymysql
import myLogger

class MysqlConnector:

    def __init__(self, host, user, password, database, logger: myLogger.MyLogger) -> None:
        """
        启动并初始化mysql连接
        """
        # 打开数据库连接
        self.db = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        # 创建一个游标对象
        self.cursor = self.db.cursor()
        self.logger = logger

    def closeMysql(self):
        """
        关闭mysql连接
        """
        print('close')
        self.db.close()

    def execute(self, sql):
        """
        做爱做的事, 没有回显
        """
        self.cursor.execute(sql)
        self.logger.logger.info(f"[SQL Execute]: {sql}\n\t Affected {self.cursor.rowcount} rows")
        return self.cursor.rowcount

    def fetch(self, sql) -> tuple:
        """
        查询数据, 如果空返回(), 如果非空返回二维元组 eg: (('example',),)
        """
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        self.logger.logger.info(f"[SQL Fetch]: {sql}\n\tGet {len(data)} rows")
        if len(data) == 0:
            print(data)
        return data

    def insertStr(self, tableName: str, content: dict) -> int:
        """
        解析字典, 插入表, 执行一次
        自动将列名加上``, 自动将空字符串转变为为NULL
        :return 影响的行数
        """
        # TODO: 实现多行插入
        column = str(tuple(content.keys())).replace("'", "`")
        values = str(tuple(content.values())).replace("''", "NULL")
        sql = f'insert into {tableName} {column} values {values};'
        self.cursor.execute(sql)
        affected = self.cursor.rowcount
        self.logger.logger.info(f"[SQL Insert]: {sql}\n\t Affected {affected} rows")
        return affected
